package user;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MemberManager {
    //返回表中所有记录


    public static void displayAllRows() throws SQLException{
        String sql="SELECT * FROM member";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                Statement st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            rs=st.executeQuery(sql); //返回结果集
            rs.last();//指针到最后一条记录
            int nRows=rs.getRow();//返回记录数
            if (nRows==0) {
                System.out.println("没有找到满足查询条件的记录！\n");
            }else {
                rs.beforeFirst(); //指针到第一条记录之前
                StringBuilder buffer=new StringBuilder(); //动态字符串
                while (rs.next()) { //遍历记录集
                    buffer.append(rs.getString("userId")).append(",");
                    buffer.append(rs.getString("passwd")).append(",");
                    buffer.append(rs.getString("answer1")).append(",");
                    buffer.append(rs.getString("answer2")).append(",");
                }//end while
                System.out.println(buffer.toString());
            }//end if
        }catch (SQLException ex) {
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end displayAllRows

    //根据id查找记录
    public static Member getRowById(String userId) throws SQLException {
        String sql="SELECT * FROM member WHERE userId=?";
        ResultSet rs=null; //结果集
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            st.setString(1, userId); //设置参数
            rs=st.executeQuery(); //返回结果集
            if (rs.next()) { //找到
                Member bean=new Member(); //定义用户数据实体
                bean.setUserId(rs.getString("userId"));
                bean.setPasswd(rs.getString("passwd"));
                bean.setAnswer1(rs.getString("answer1"));
                bean.setAnswer2(rs.getString("answer2"));
                return bean; //返回用户数据实体
            }else { //没找到
                return null;
            }
        } catch (SQLException ex) {
            return null;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end getRowById
    //注册用户
    public static boolean registerUser(Member bean) throws SQLException{
        if (getRowById(bean.getUserId())!=null) return false;//如果用户存在，则注册失败
        String sql="INSERT INTO member (userId,passwd,answer1,answer2) VALUES (?,?,?,?)";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            st.setString(1,bean.getUserId());
            st.setString(2, bean.getPasswd());
            st.setString(3, bean.getAnswer1());
            st.setString(4, bean.getAnswer2());
            int affected=st.executeUpdate();
            return affected==1; //注册成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
        }//end try
    }//end registerUser
    //用户登录
    //对比账号和答案1
    public static boolean passfind(Member bean) throws SQLException{
        String sql="SELECT * FROM member WHERE userId=? AND answer1=? AND answer2=?";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUserId());
            st.setString(3, bean.getAnswer1());
            st.setString(4, bean.getAnswer2());
            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    public static boolean testId(Member bean) throws SQLException{
        String sql="SELECT * FROM member WHERE userId=? ";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUserId());

            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }

    public static boolean userLogin(Member bean) throws SQLException {
        String sql="SELECT * FROM member WHERE userId=? AND passwd=?";
        ResultSet rs=null;
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql,
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ){
            //设置参数
            st.setString(1,bean.getUserId());
            st.setString(2, bean.getPasswd());
            rs=st.executeQuery(); //返回结果集
            return rs.next(); //登录成功或失败
        } catch (SQLException ex) {
            return false;
        }finally {
            if (rs!=null) rs.close();
        }//end try
    }//end userLogin
    //修改用户
    public static boolean updateUser(Member bean) {
        String sql="UPDATE member SET name=? , password= ? ,"
                + " email=? , headimage= ? , time= ? WHERE id=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            //设置参数
            st.setString(1, bean.getUserId());
            st.setString(2, bean.getPasswd());
            st.setString(3, bean.getAnswer1());
            st.setString(4,bean.getAnswer2());
            int affected=st.executeUpdate();
            return affected==1; //修改成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
    //删除用户
    public static boolean deleteUser(String userId) {
        String sql="DELETE FROM member WHERE id=?";
        try (
                Connection conn=DBUtils.getConnection();
                PreparedStatement st=conn.prepareStatement(sql);
        ){
            st.setString(1,userId);
            int affected=st.executeUpdate();
            return affected==1; //删除成功或失败
        } catch (SQLException ex) {
            return false;
        }//end try
    }//end updateUser
}//end class